<?php

	include("pub/conn.php");
	include("pub/functions.php");

	$do = isset($_POST['do']) ? $_POST['do'] : '';

	if($do == "ty_classes"){
		// 月度预约及体验课预约数据 
		$mon = isset($_POST['mon']) ? $_POST['mon'] : ''; // 查询的月份
		$id_type = isset($_POST['type']) ? $_POST['type'] : ''; // type 取值： region_id， city_id， gym_id
		$id = isset($_POST['id']) ? $_POST['id'] : ''; // id 对应各种type取值下的对应值
		$sql = "
			SELECT d.`day`, r.region_id, r.city_id,  sum(d.user_classes) as pre, sum(d.ty_user_classes) as ty_pre
			FROM tj.gym_days d
			inner join region_gym r on d.gym_id = r.gym_id
			where date_format(d.`day`, '%Y-%m') = '$mon' 
			and (r.$id_type = '$id' or '$id_type=$id' = 'region_id=0')
			group by d.`day`
		";	
	}

	if($do == "ty_classes_m"){

		$type = isset($_POST['type']) ? $_POST['type'] : 'region';
		$target = isset($_POST['target']) ? $_POST['target'] : '0';

		$sql = "
			SELECT d.`day`, r.region_id, r.city_id,  sum(d.user_classes) as pre, sum(d.ty_user_classes) as ty_pre
			FROM tj.gym_days d
			inner join region_gym r on d.gym_id = r.gym_id
			where (
				'$type' = 'region' and '$target' = '0' or
				'$type' = 'region' and r.region_id = '$target' or
				'$type' = 'city' and r.city_id = '$target' or
				'$type' = 'gym' and r.gym_id = '$target' 
			)
			group by d.`day`
		";
	}

	if($do == "ty_classes_table"){ // 查询全国各城市，区域各城市，全国各门店，区域各门店，城市各门店： 所有预约、体验课预约、体验课上课情况
		$day = isset($_POST['day']) ? $_POST['day'] : ''; // 查询的日期
		$region_id = isset($_POST['region_id']) ? $_POST['region_id'] : 0; // 查询区域范围
		$city = isset($_POST['city']) ? $_POST['city'] : ""; // 当查询对像为城市时，传城市名称
		$count_type = isset($_POST['count_type']) ? $_POST['count_type'] : 'gym'; // count_type 取值： city, gym
		if($count_type == 'city'){
			$target = 'city';
			$grouper = 'city_id';
		}else{
			$target = '`name`';
			$grouper = 'g.id';
		}
		//$id = isset($_POST['id']) ? $_POST['id'] : ''; // id 对应各种type取值下的对应值
		$sql = "
			SELECT date_format(d.`day`, '%Y-%m-%d') as `day`, g.$target as target, sum(d.user_classes) pre, sum(d.ty_user_classes) ty_pre,
				sum(d.ty_users) as ty_checked
			from tj.gym_days d
			inner join gym g on d.gym_id = g.id
			inner join region_gym rg on rg.gym_id = g.id
			where date_format(d.`day`, '%Y-%m-%d') = '$day' and (rg.region_id = $region_id or $region_id = 0) 
				and ( g.city = '$city' or '$city' = '')
			group by $grouper
			having pre > 0
		";
	}




	if($do == "ty_classes_table_gym"){ // 查询指定门店，当天各节课，预约，体验课预约及体验课上课情况
		$day = isset($_POST['day']) ? $_POST['day'] : ''; // 查询的日期
		$gymName = isset($_POST['gymName']) ? $_POST['gymName'] : '';
		$sql = "
			SELECT c.start_time, g.name as target, count(*) as pre, sum(case when o.all_times = 1 then 1 else 0 end) as ty_pre, 
				sum(case when o.all_times = 1 and e.has_checkin = 1 then 1 else 0 end) as ty_checked
			from orders o
			inner join user_subject s on o.order_uuid = s.order_uuid
			inner join user_classes e on e.user_subject_uuid = s.uuid
			inner join classes c on e.classes_id = c.id			
			inner join gym g on c.gym_id = g.id
			where date_format(c.start_time, '%Y-%m-%d') = '$day' and g.`name` = '$gymName'
			group by c.start_time
		";


	}

	if($do == "getGymRegions"){
		$region_id = isset($_POST['region_id']) ? $_POST['region_id'] : 0; // 查询的月份

		//$id = isset($_POST['id']) ? $_POST['id'] : ''; // id 对应各种type取值下的对应值
		$sql = "
			SELECT r.id as regionId, r.`name` as regionName, rg.city_id as cityId, g.city as cityName, g.`name` as gymName, g.id as gymId
			from gym g
			inner join region_gym rg on rg.gym_id = g.id
			inner join region r on r.id = rg.region_id
			where g.status = 1 and (rg.region_id = $region_id or $region_id = 0)
			order by r.id, g.city
		";
	}


	echo query_sql($sql, $conn);

?>